package com.shujia.jdbc;

import com.alibaba.druid.pool.DruidDataSource;
import org.junit.Test;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

public class JdbcUtil {

    private static DruidDataSource dataSource = new DruidDataSource();
    public static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();

    // 初始化连接池
    static {
        try {
            InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream("config\\druid.properties");
            Properties prop = new Properties();
            prop.load(is);

            dataSource.setUrl(prop.getProperty("jdbcUrl"));
            dataSource.setUsername(prop.getProperty("username"));
            dataSource.setPassword(prop.getProperty("password"));
            dataSource.setInitialSize(Integer.valueOf(prop.getProperty("initialSize")));
            dataSource.setMaxActive(Integer.valueOf(prop.getProperty("maxActive")));
            dataSource.setMinIdle(Integer.valueOf(prop.getProperty("minldle")));
            dataSource.setMaxWait(Integer.valueOf(prop.getProperty("maxWait")));

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 从连接池里获取连接对象并返回
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }


    public static void close(Connection connection, Statement statement) {
        try {
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    @Test
    public void slectListTest() {
        String sql = "select * from t_user";
        List<User> list = slectList(sql, User.class);
        System.out.println(list);
    }

    /*
    封装公共的查询功能
     */
    public <T> List<T> slectList(String sql, Class<T> c, Object... args) {
        Connection connection = null;
        PreparedStatement ps = null;
        try {

            connection = JdbcUtil.getConnection();
            ps = connection.prepareStatement(sql);

            for (int i = 0; i < args.length; i++) {
                // 把数组里面的元素填充到 PreparedStatement 对象里
                ps.setObject(i + 1, args[i]);
            }

            // 获取查询的结果集
            ResultSet rs = ps.executeQuery();

            List<T> list = new ArrayList<>();

            // 获取结果的元数据
            ResultSetMetaData metaData = rs.getMetaData();

            // 获取列的数量
            int columnCount = metaData.getColumnCount();
            System.out.println(columnCount);


            // 遍历每行, 从每行数据里获取每列的数据
            while (rs.next()) {
                // 每一行对应一个对象
                T t = c.newInstance();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);

                    Object colmValue = rs.getObject(columnName);
                    System.out.println(colmValue);


                    if (colmValue == null) {
                        break;
                    }

                    // 反射获取类的所有属性
                    Field[] fields = c.getDeclaredFields();

                    // 如果列名和属性名相同, 自动填充值
                    for (Field field : fields) {
                        String newColum = columnName.replace("_", "").toLowerCase();
                        if (field.getName().toLowerCase().equals(newColum)) {
                            field.setAccessible(true);
                            field.set(t, colmValue);
                        }
                    }

                }
                System.out.println("-----------------");
                list.add(t);
            }

            return list;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(connection, ps);
        }
        return null;
    }


    /*
    通用的修改方法, 兼容事务
     */
    public static void update(String sql, Object... args) {
        // 获取连接
        Connection connection = threadLocal.get();

        boolean flag = connection == null;

        if (flag) { // 方法不需要事务, 在方法里面创建连接
            try {
                connection = dataSource.getConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        // 编译有占位符的sql
        try (PreparedStatement ps = connection.prepareStatement(sql)) {


            // 设置参数
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            // 执行sql语句
            int num = ps.executeUpdate();

            System.out.println("影响了" + num + "行记录");

        } catch (SQLException e) {
            e.printStackTrace();


        } finally {
            if (flag) {
                // 关闭连接
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                }
            }
        }
    }

    public static void changeRecordSafe(String sql, Object... args) {
        // 获取连接
        Connection connection = threadLocal.get();
        try (
                // 编译有占位符的sql
                PreparedStatement ps = connection.prepareStatement(sql)) {
            // 获取数据库操作对象（专门执行sql语句的对象）

            // 设置参数
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            // 执行sql语句
            int num = ps.executeUpdate();

            System.out.println("影响了" + num + "行记录");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}
